CREATE PROCEDURE [dbo].[BAEImisSubscriptionsGetAllByUser] @UserId AS
varchar(10)
AS
    -- Retrieve accrual dues billed to the ID (checking that sys config option 'BillingEnableAccrualOnWeb' is enabled)
    SELECT s.[ID], s.[PRODUCT_CODE], s.[BT_ID], s.[PROD_TYPE], s.[STATUS], s.[BEGIN_DATE], s.[PAID_THRU], s.[COPIES],
           s.[SOURCE_CODE], s.[FIRST_SUBSCRIBED], s.[CONTINUOUS_SINCE], s.[PRIOR_YEARS], s.[FUTURE_COPIES],
           s.[FUTURE_COPIES_DATE], s.[PREF_MAIL], s.[PREF_BILL], s.[RENEW_MONTHS], s.[MAIL_CODE], s.[PREVIOUS_BALANCE],
           s.[BILL_DATE], s.[REMINDER_DATE], s.[REMINDER_COUNT], s.[BILL_BEGIN], s.[BILL_THRU], s.[BILL_AMOUNT],
           s.[BILL_COPIES], s.[PAYMENT_AMOUNT], s.[PAYMENT_DATE], s.[PAID_BEGIN], s.[LAST_PAID_THRU], s.[COPIES_PAID],
           s.[ADJUSTMENT_AMOUNT], s.[LTD_PAYMENTS], s.[ISSUES_PRINTED], s.[CANCEL_REASON], s.[YEARS_ACTIVE_STRING],
           s.[LAST_ISSUE], s.[LAST_ISSUE_DATE], s.[DATE_ADDED], s.[LAST_UPDATED], s.[UPDATED_BY], s.[INTENT_TO_EDIT],
           s.[FLAG], s.[BILL_TYPE], s.[COMPLIMENTARY], s.[FUTURE_CREDITS], s.[INVOICE_REFERENCE_NUM], s.[INVOICE_LINE_NUM],
           s.[CAMPAIGN_CODE], s.[APPEAL_CODE], s.[ORG_CODE], s.[IS_FR_ITEM], s.[FAIR_MARKET_VALUE], s.[TIME_STAMP],
           il.[BALANCE], il.[CHARGES], il.[CREDITS], CAST(1 AS BIT) AS [IsAccrual]
      FROM dbo.[Subscriptions] s
           INNER JOIN dbo.[Invoice] i ON s.[INVOICE_REFERENCE_NUM] = i.[REFERENCE_NUM]
           INNER JOIN dbo.[Invoice_Lines] il ON s.[INVOICE_REFERENCE_NUM] = il.[REFERENCE_NUM] AND s.[INVOICE_LINE_NUM] = il.[LINE_NUM]
     WHERE s.INVOICE_LINE_NUM != 0
           AND i.BT_ID = @UserId
           AND 'True' = (SELECT sc.[ParameterValue]
                           FROM dbo.[SystemConfig] sc
                          WHERE sc.[SystemConfigKey] = 'EF92F6C4-7D6F-4EC9-B656-1AFD7D17442D')
    
    UNION
    
    -- Retrieve cash dues for the ID (regardless of who it's billed to)
    SELECT s.[ID], s.[PRODUCT_CODE], s.[BT_ID], s.[PROD_TYPE], s.[STATUS], s.[BEGIN_DATE], s.[PAID_THRU], s.[COPIES],
           s.[SOURCE_CODE], s.[FIRST_SUBSCRIBED], s.[CONTINUOUS_SINCE], s.[PRIOR_YEARS], s.[FUTURE_COPIES],
           s.[FUTURE_COPIES_DATE], s.[PREF_MAIL], s.[PREF_BILL], s.[RENEW_MONTHS], s.[MAIL_CODE], s.[PREVIOUS_BALANCE],
           s.[BILL_DATE], s.[REMINDER_DATE], s.[REMINDER_COUNT], s.[BILL_BEGIN], s.[BILL_THRU], s.[BILL_AMOUNT],
           s.[BILL_COPIES], s.[PAYMENT_AMOUNT], s.[PAYMENT_DATE], s.[PAID_BEGIN], s.[LAST_PAID_THRU], s.[COPIES_PAID],
           s.[ADJUSTMENT_AMOUNT], s.[LTD_PAYMENTS], s.[ISSUES_PRINTED], s.[CANCEL_REASON], s.[YEARS_ACTIVE_STRING],
           s.[LAST_ISSUE], s.[LAST_ISSUE_DATE], s.[DATE_ADDED], s.[LAST_UPDATED], s.[UPDATED_BY], s.[INTENT_TO_EDIT],
           s.[FLAG], s.[BILL_TYPE], s.[COMPLIMENTARY], s.[FUTURE_CREDITS], s.[INVOICE_REFERENCE_NUM], s.[INVOICE_LINE_NUM],
           s.[CAMPAIGN_CODE], s.[APPEAL_CODE], s.[ORG_CODE], s.[IS_FR_ITEM], s.[FAIR_MARKET_VALUE], s.[TIME_STAMP],
           s.[BALANCE], 0, 0, CAST(0 AS BIT) AS [IsAccrual]  
      FROM dbo.[Subscriptions] s
           INNER JOIN dbo.[Product] p ON s.[PRODUCT_CODE] = p.[PRODUCT_CODE]
     WHERE s.[INVOICE_LINE_NUM] = 0
           AND s.[ID] = @UserId
           AND p.[WEB_OPTION] = 1



GO